package zy.dao.buy.settle.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.settle.BuySettleDAO;
import zy.entity.buy.enter.T_Buy_Enter;
import zy.entity.buy.fee.T_Buy_Fee;
import zy.entity.buy.settle.T_Buy_Settle;
import zy.entity.buy.settle.T_Buy_SettleList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class BuySettleDAOImpl extends BaseDaoImpl implements BuySettleDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object st_ar_state = params.get("st_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object st_supply_code = params.get("st_supply_code");
		Object st_manager = params.get("st_manager");
		Object st_number = params.get("st_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_buy_settle t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = st_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(st_ar_state)) {
			sql.append(" AND st_ar_state = :st_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND st_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND st_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(st_supply_code)) {
			sql.append(" AND st_supply_code = :st_supply_code ");
		}
		if (StringUtil.isNotEmpty(st_manager)) {
			sql.append(" AND st_manager = :st_manager ");
		}
		if (StringUtil.isNotEmpty(st_number)) {
			sql.append(" AND INSTR(st_number,:st_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Buy_Settle> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object st_ar_state = params.get("st_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object st_supply_code = params.get("st_supply_code");
		Object st_manager = params.get("st_manager");
		Object st_number = params.get("st_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_supply_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_paid,st_paidmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_settle t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = st_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(st_ar_state)) {
			sql.append(" AND st_ar_state = :st_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND st_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND st_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(st_supply_code)) {
			sql.append(" AND st_supply_code = :st_supply_code ");
		}
		if (StringUtil.isNotEmpty(st_manager)) {
			sql.append(" AND st_manager = :st_manager ");
		}
		if (StringUtil.isNotEmpty(st_number)) {
			sql.append(" AND INSTR(st_number,:st_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY st_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_Settle.class));
	}

	@Override
	public T_Buy_Settle load(Integer st_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_supply_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_paid,st_paidmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_settle t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = st_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE st_id = :st_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("st_id", st_id),
					new BeanPropertyRowMapper<>(T_Buy_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Settle load(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_supply_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_paid,st_paidmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_settle t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = st_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE st_number = :st_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("st_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Buy_Settle check(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_supply_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_paid,st_paidmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid");
		sql.append(" FROM t_buy_settle t");
		sql.append(" WHERE st_number = :st_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("st_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Settle check_settle(String sp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id");
		sql.append(" FROM t_buy_settle");
		sql.append(" WHERE st_ar_state != 1");
		sql.append(" AND st_supply_code = :sp_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public List<T_Buy_SettleList> load_buy_forsavetemp(String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT * FROM");
		sql.append(" (SELECT et_money AS stl_payable,");
		sql.append(" et_payabled AS stl_payabled,");
		sql.append(" et_discount_money AS stl_discount_money_yet,");
		sql.append(" et_prepay AS stl_prepay_yet,");
		sql.append(" et_payable AS stl_unpayable,");
		sql.append(" 0 AS stl_discount_money,");
		sql.append(" 0 AS stl_prepay,");
		sql.append(" 0 AS stl_real_pay,");
		sql.append(" et_number AS stl_bill_number,");
		sql.append(" et_type AS stl_type,");
		sql.append(" (CASE WHEN et_type = 0 THEN 3");
		sql.append(" WHEN et_type = 1 THEN 1");
		sql.append(" WHEN et_type = 2 THEN IF(et_payable<0,1,2)");
		sql.append(" ELSE 4 END) AS new_order");
		sql.append(" FROM t_buy_enter");
		sql.append(" WHERE 1=1");
		sql.append(" AND et_supply_code = :sp_code");
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND et_pay_state != 2");
		sql.append(" AND companyid = :companyid");
		sql.append(" UNION ALL ");
		sql.append(" SELECT fe_money AS stl_payable,");
		sql.append(" fe_payabled AS stl_payabled,");
		sql.append(" fe_discount_money AS stl_discount_money_yet,");
		sql.append(" fe_prepay AS stl_prepay_yet,");
		sql.append(" fe_payable AS stl_unpayable,");
		sql.append(" 0 AS stl_discount_money,");
		sql.append(" 0 AS stl_prepay,");
		sql.append(" 0 AS stl_real_pay,");
		sql.append(" fe_number AS stl_bill_number,");
		sql.append(" 3 AS stl_type,");
		sql.append(" IF(fe_payable<0,1,3) AS new_order");
		sql.append(" FROM t_buy_fee");
		sql.append(" WHERE 1=1");
		sql.append(" AND fe_supply_code = :sp_code");
		sql.append(" AND fe_ar_state = 1");
		sql.append(" AND fe_pay_state != 2");
		sql.append(" AND companyid = :companyid");
		sql.append(" )t");
		sql.append(" ORDER BY new_order ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_SettleList.class));
	}

	@Override
	public List<T_Buy_SettleList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,stl_join,stl_us_id,companyid ");
		sql.append(" FROM t_buy_settlelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY stl_id ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_SettleList.class));
	}
	
	@Override
	public List<T_Buy_SettleList> temp_list_forsave(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,stl_join,stl_us_id,companyid ");
		sql.append(" FROM t_buy_settlelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_discount_money+stl_prepay+stl_real_pay != 0");
		sql.append(" AND stl_join = 1");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY stl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_SettleList.class));
	}
	
	@Override
	public void temp_save(List<T_Buy_SettleList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_buy_settlelist_temp");
		sql.append(" (stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,stl_join,stl_us_id,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:stl_payable,:stl_payabled,:stl_discount_money_yet,:stl_prepay_yet,:stl_unpayable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_pay,:stl_bill_number,:stl_remark,:stl_type,:stl_join,:stl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(List<T_Buy_SettleList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_discount_money = :stl_discount_money");
		sql.append(" ,stl_prepay = :stl_prepay");
		sql.append(" ,stl_real_pay = :stl_real_pay");
		sql.append(" ,stl_join = :stl_join");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_clear(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_buy_settlelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("stl_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public void temp_updateDiscountMoney(T_Buy_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_discount_money = :stl_discount_money");
		if(temp.getStl_real_pay() != null){
			sql.append(" ,stl_real_pay = :stl_real_pay");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updatePrepay(T_Buy_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_prepay = :stl_prepay");
		if(temp.getStl_real_pay() != null){
			sql.append(" ,stl_real_pay = :stl_real_pay");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRealMoney(T_Buy_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_real_pay = :stl_real_pay");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRemark(T_Buy_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_remark = :stl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateJoin(String ids,String stl_join) {
		String[] idArr = ids.split(",");
		MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
		sqlParameterSource.addValue("stl_join", stl_join);
		if (idArr.length > 1) {
			sqlParameterSource.addValue("stl_id", Arrays.asList(idArr));
		} else {
			sqlParameterSource.addValue("stl_id", ids);
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_settlelist_temp");
		sql.append(" SET stl_join = :stl_join");
		sql.append(" WHERE 1=1");
		if (idArr.length > 1) {
			sql.append(" AND stl_id IN (:stl_id)");
		}else {
			sql.append(" AND stl_id = :stl_id");
		}
		namedParameterJdbcTemplate.update(sql.toString(), sqlParameterSource);
	}
	
	@Override
	public List<T_Buy_SettleList> detail_list(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_number,stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,companyid");
		sql.append(" FROM t_buy_settlelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY stl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Buy_SettleList.class));
	}
	
	@Override
	public void save(T_Buy_Settle settle, List<T_Buy_SettleList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_BUY_SETTLE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(st_number))) AS new_number");
		sql.append(" FROM t_buy_settle");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(st_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", settle.getCompanyid()), String.class);
		settle.setSt_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_settle");
		sql.append(" (st_number,st_date,st_supply_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_paid,st_paidmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:st_number,:st_date,:st_supply_code,:st_maker,:st_manager,:st_ba_code,:st_discount_money,:st_prepay,:st_paid,:st_paidmore,");
		sql.append(" :st_remark,:st_ar_state,:st_ar_date,:st_entire,:st_pp_number,:st_leftdebt,:st_sysdate,:st_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle),holder);
		settle.setSt_id(holder.getKey().intValue());
		for(T_Buy_SettleList item:details){
			item.setStl_number(settle.getSt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_settlelist");
		sql.append(" (stl_number,stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:stl_number,:stl_payable,:stl_payabled,:stl_discount_money_yet,:stl_prepay_yet,:stl_unpayable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_pay,:stl_bill_number,:stl_remark,:stl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Buy_Settle settle, List<T_Buy_SettleList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_settle");
		sql.append(" SET st_date=:st_date");
		sql.append(" ,st_maker=:st_maker");
		sql.append(" ,st_manager=:st_manager");
		sql.append(" ,st_ba_code=:st_ba_code");
		sql.append(" ,st_discount_money=:st_discount_money");
		sql.append(" ,st_prepay=:st_prepay");
		sql.append(" ,st_paid=:st_paid");
		sql.append(" ,st_paidmore=:st_paidmore");
		sql.append(" ,st_remark=:st_remark");
		sql.append(" ,st_ar_state=:st_ar_state");
		sql.append(" ,st_ar_date=:st_ar_date");
		sql.append(" ,st_entire=:st_entire");
		sql.append(" ,st_pp_number=:st_pp_number");
		sql.append(" ,st_leftdebt=:st_leftdebt");
		sql.append(" ,st_us_id=:st_us_id");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
		for(T_Buy_SettleList item:details){
			item.setStl_number(settle.getSt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_settlelist");
		sql.append(" (stl_number,stl_payable,stl_payabled,stl_discount_money_yet,stl_prepay_yet,stl_unpayable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_pay,stl_bill_number,stl_remark,stl_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:stl_number,:stl_payable,:stl_payabled,:stl_discount_money_yet,:stl_prepay_yet,:stl_unpayable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_pay,:stl_bill_number,:stl_remark,:stl_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Buy_Settle settle) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_settle");
		sql.append(" SET st_ar_state=:st_ar_state");
		sql.append(" ,st_ar_date = :st_ar_date");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
	}
	
	@Override
	public void updatePpNumber(T_Buy_Settle settle) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_settle");
		sql.append(" SET st_pp_number=:st_pp_number");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
	}
	
	@Override
	public List<T_Buy_Enter> listEnterBySettle(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_money,");
		sql.append(" et_discount_money + stl_discount_money AS et_discount_money,");
		sql.append(" et_prepay + stl_prepay AS et_prepay,");
		sql.append(" et_payabled + stl_real_pay AS et_payabled,");
		sql.append(" et_payable - (stl_discount_money+stl_prepay+stl_real_pay) AS et_payable");
		sql.append(" FROM t_buy_settlelist t");
		sql.append(" JOIN t_buy_enter et ON et_number = stl_bill_number AND et.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type != 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_Enter.class));
	}
	
	@Override
	public List<T_Buy_Enter> listEnterBySettle_Reverse(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT et_id,et_money,");
		sql.append(" et_discount_money - stl_discount_money AS et_discount_money,");
		sql.append(" et_prepay - stl_prepay AS et_prepay,");
		sql.append(" et_payabled - stl_real_pay AS et_payabled,");
		sql.append(" et_payable + (stl_discount_money+stl_prepay+stl_real_pay) AS et_payable");
		sql.append(" FROM t_buy_settlelist t");
		sql.append(" JOIN t_buy_enter et ON et_number = stl_bill_number AND et.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type != 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_Enter.class));
	}
	
	@Override
	public List<T_Buy_Fee> listFeeBySettle(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_money,");
		sql.append(" fe_discount_money + stl_discount_money AS fe_discount_money,");
		sql.append(" fe_prepay + stl_prepay AS fe_prepay,");
		sql.append(" fe_payabled + stl_real_pay AS fe_payabled,");
		sql.append(" fe_payable - (stl_discount_money+stl_prepay+stl_real_pay) AS fe_payable ");
		sql.append(" FROM t_buy_settlelist t");
		sql.append(" JOIN t_buy_fee fe ON fe_number = stl_bill_number AND fe.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type = 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_Fee.class));
	}
	
	@Override
	public List<T_Buy_Fee> listFeeBySettle_Reverse(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_money,");
		sql.append(" fe_discount_money - stl_discount_money AS fe_discount_money,");
		sql.append(" fe_prepay - stl_prepay AS fe_prepay,");
		sql.append(" fe_payabled - stl_real_pay AS fe_payabled,");
		sql.append(" fe_payable + (stl_discount_money+stl_prepay+stl_real_pay) AS fe_payable ");
		sql.append(" FROM t_buy_settlelist t");
		sql.append(" JOIN t_buy_fee fe ON fe_number = stl_bill_number AND fe.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type = 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_Fee.class));
	}
	
	@Override
	public void updateEnterBySettle(List<T_Buy_Enter> enters) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_enter");
		sql.append(" SET et_discount_money = :et_discount_money");
		sql.append(" ,et_prepay = :et_prepay");
		sql.append(" ,et_payabled = :et_payabled");
		sql.append(" ,et_payable = :et_payable");
		sql.append(" ,et_pay_state = :et_pay_state");
		sql.append(" WHERE 1=1");
		sql.append(" AND et_id = :et_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(enters.toArray()));
	}
	
	@Override
	public void updateFeeBySettle(List<T_Buy_Fee> fees) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_fee");
		sql.append(" SET fe_discount_money = :fe_discount_money");
		sql.append(" ,fe_prepay = :fe_prepay");
		sql.append(" ,fe_payabled = :fe_payabled");
		sql.append(" ,fe_payable = :fe_payable");
		sql.append(" ,fe_pay_state = :fe_pay_state");
		sql.append(" WHERE 1=1");
		sql.append(" AND fe_id = :fe_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(fees.toArray()));
	}
	
	@Override
	public void del(String st_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_settle");
		sql.append(" WHERE st_number=:st_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("st_number", st_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_buy_settlelist");
		sql.append(" WHERE stl_number=:stl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("stl_number", st_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String st_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_settlelist");
		sql.append(" WHERE stl_number=:stl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("stl_number", st_number).addValue("companyid", companyid));
	}
	
	@Override
	public T_Buy_SettleList check_settle_bill(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_number");
		sql.append(" FROM t_buy_settlelist");
		sql.append(" WHERE stl_bill_number = :number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_SettleList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
}
